package webSiteDataStore;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

public class SQLExecute
{
    private static final Logger theLogger = Logger.getLogger(SQLExecute.class.getName());

    /*
     * Important:
     * Any consumer of this method must close the PreparedStatement and Connection resources returned by this method.
     * Connection should be retrieved by the PreparedStatement#getConnection() method.
     */
    public static PreparedStatement Select(String table)
    {
        return Select(table, null, null, null);
    }

    /*
     * Important:
     * Any consumer of this method must close the PreparedStatement and Connection resources returned by this method.
     * Connection should be retrieved by the PreparedStatement#getConnection() method.
     */
    public static PreparedStatement Select(String table, ArrayList<String> columns, String where, ArrayList<String> whereValues)
    {
        PreparedStatement preparedStatement = null;

        try
        {
            Connection connection = ConnectionPool.getConnection();
            StringBuilder columnsList = new StringBuilder("");
            if (columns == null)
                columnsList.append("*");
            else
            {
               for (int i = 0 ; i < columns.size() ; ++i)
               {
                   columnsList.append(columns.get(i) + ((i < columns.size() - 1) ? ", " : " "));
               }
            }


            StringBuilder sqlStatement = new StringBuilder("SELECT ").append(columnsList).append(" FROM ").append(table);
            if (where != null)
                sqlStatement.append(" WHERE ").append(where);

            sqlStatement.append(";");

            preparedStatement = connection.prepareStatement(sqlStatement.toString());

            if (where != null)
            {
               for (int i = 0 ; i < whereValues.size() ; ++i)
               {
                   Object value = whereValues.get(i);
                   if (value instanceof String)
                   {
                       preparedStatement.setString(i+1, (String)value);
                   }
                   else if (value instanceof Integer)
                   {
                       preparedStatement.setInt(i+1, (Integer)value);
                   }
                   else if (value instanceof java.util.Date)
                   {
                       java.util.Date javaDate = (Date)value;
                       java.sql.Date sqlDate = new java.sql.Date(javaDate.getTime());
                       preparedStatement.setDate(i+1, sqlDate);
                   }
               }
            }

            preparedStatement.executeQuery();
            
        }
        catch(SQLException e)
        {
            theLogger.log(Level.SEVERE, null, e);
            if (preparedStatement != null)
            {
                SafeFreeResource.closeStatement(preparedStatement);
                preparedStatement = null;
            }

        }
        return preparedStatement;
    }

    public static boolean Insert(String table, ArrayList<String> columns, ArrayList<Object> values)
    {
       Connection connection = null;    
       PreparedStatement preparedStatement = null;
       boolean success = false;

       try
       {
           connection = ConnectionPool.getConnection();
           StringBuilder sqlStatement = new StringBuilder("INSERT INTO ").append(table).append(" (");
           for (int i = 0 ; i < columns.size() ; ++i)
           {
               sqlStatement.append(columns.get(i) + ((i < columns.size() - 1) ? ", " : ") "));
           }
           sqlStatement.append(" VALUES(");

           for (int i = 0 ; i < values.size() ; ++i)
           {
               sqlStatement.append("?" + ((i < values.size() - 1) ? "," : ");"));
           }

           preparedStatement = connection.prepareStatement(sqlStatement.toString());

           for (int i = 0 ; i < values.size() ; ++i)
           {
               Object value = values.get(i);
               if (value instanceof String)
               {
                   preparedStatement.setString(i+1, (String)value);
               }
               else if (value instanceof Integer)
               {
                   preparedStatement.setInt(i+1, (Integer)value);
               }
               else if (value instanceof java.util.Date)
               {
                   java.util.Date javaDate = (Date)value;
                   java.sql.Date sqlDate = new java.sql.Date(javaDate.getTime());
                   preparedStatement.setDate(i+1, sqlDate);
               }
           }

           int rowsAffected = preparedStatement.executeUpdate();
           success = (rowsAffected == 1);
           if(!success)
           {
                StringBuilder msg = sqlStatement.append(" generated an error: ").append(rowsAffected).append(" rows were affected.");
                Logger.getLogger(UserData.class.getName()).log(Level.SEVERE, msg.toString());
           }
        }
        catch(SQLException e)
        {
            theLogger.log(Level.SEVERE, null, e);
        }
        finally
        {
            SafeFreeResource.closeStatement(preparedStatement);
            SafeFreeResource.closeConnection(connection);
        }
        return success;
    }
}
